/*
 *  Copyright 2008 The MITRE Corporation (http://www.mitre.org/). All Rights Reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */


package org.mitre.lattice.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.mitre.lattice.util.SqlTemplates;
import org.mitre.mrald.parser.ParserElement;
import org.mitre.mrald.util.FormTags;

/**
 *  Description of the Class
 *
 *@author     ghamilton
 *@created    January 9, 2004
 */
public class LabelTableElement extends ParserElement {
	/**
	 *  Constructor for the LatticeElement object
	 *
	 *@since
	 */

        private String tableName=null;
	private ArrayList<String> primaryKeys = new ArrayList<String>();
	private ArrayList<String> dataTypeList = new ArrayList<String>();
	private ArrayList<Integer> sqlTypeList = new ArrayList<Integer>();

	private String insertStmnt = new String();
	private String selectRows = new String();

	public LabelTableElement() { }


	/**
	 *  Gets the elementType of the ParserElement-derived object
	 *
	 *@return    The elementType value
	 */
	public String getElementType() {
		return "Label Table Element";
	}


	/**
	 *  Gets the elementType of the ParserElement-derived object
	 *
	 *@return    The elementType value
	 */
	public String process() {
		return "Label Table Element";
	}


	/**
	 *  Gets the elementType of the ParserElement-derived object
	 *
	 *@return    The elementType value
	 */
	public String getType() {
		return "Label Table Element";
	}


	/*
	 *  The following statements need to be created to allow for labelling
	 *  CREATE TABLE <TableName>_Label (
	 *  <PKColumn1> <PKColType> NOT NULL,
	 *  Label character varying(50),
	 *  CONSTRAINT sys_<tableName>_Label_CHK CHECK ((<PKcolumn1> IS NOT NULL)),
	 *  Constraint sys_<tableName>_Label_PK Primary Key (<PKcolumn1>) ,
	 *  Constraint sys_<tableName>_Label_FK Foreign Key (<PKcolumn1>) REFERENCES <tableName>(<PKcolumn1>)
	 *  );
	 *  CREATE RULE <tableName>_Label_Insert AS ON INSERT TO <TableName> DO
	 *  INSERT INTO <tableName>_Label VALUES(new.<PKCOLUMNS>, 'NOT SET');
	 *  CREATE RULE <tableName>_Label_Delete AS ON DELETE TO <TableName> DO
	 *  DELETE FROM <tableName>_Label WHERE <PKCOLUMN1> = old.<PKCOLUMN1> AND <PKCOLUMN2> = old.<PKCOLUMN2>;
	 *  INSERT INTO LABEL_SQL VALUES ('<tableName>', ' <TableName>.<PKCOLUMN1> = <TableName>_Label.<PKCOLUMN1>  AND <TableName>_Label.Label IN ');
	 **/
	/**
	 *  Sets the sQLStatements attribute of the LabelTableElement object
	 *  Using the tableNames and types
	 *
	 *@return    Returns an ArrayList of statements to be executed.
	 */
	public ArrayList buildSql()
	{
		ArrayList<String> queries = new ArrayList<String>();
		String query = new String();
		StringBuffer pkCols = new StringBuffer(" " );
		StringBuffer constrStr = new StringBuffer();

		String insertRule = "CREATE RULE " + tableName + "_Label_Insert AS ON INSERT TO " +  tableName + " DO INSERT INTO " + tableName + "_Label VALUES(";
		String deleteRule = "CREATE RULE " + tableName + "_Label_Delete AS ON DELETE TO " +  tableName + " DO DELETE FROM " + tableName + "_Label WHERE ";

		String labelSql = "INSERT INTO LABEL_SQL VALUES ('" + tableName + "','";

		query = "CREATE TABLE " +  tableName + "_Label ( ";

		for (int i=0; i < primaryKeys.size(); i++)
		{

			String pkCol =  primaryKeys.get(i).toString();
			query= query + pkCol + " " + dataTypeList.get(i).toString() + " NOT NULL, ";

			pkCols.append( pkCol);
			labelSql = labelSql + tableName + "." + pkCol + " = " + tableName + "_Label." + pkCol + " AND ";

			insertRule = insertRule + " new." + pkCol + ",";
			deleteRule = deleteRule + pkCol + "= old." + pkCol ;


			if (i < (primaryKeys.size() - 1) )
			{
			    pkCols.append(", ");
			    deleteRule = deleteRule + " AND ";
			}

			constrStr.append( " CONSTRAINT sys_" + tableName + "_Label_CHK00" + i + " CHECK ((" + pkCol + " IS NOT NULL))," );

		}

		constrStr.append( " CONSTRAINT sys_" + tableName + "_Label_CHK00" + primaryKeys.size() + " CHECK (('label' IS NOT NULL))," );

		String pkList = " ( " + pkCols + ", 'label') ";
		String pkStr="Constraint sys_"+ tableName + "_Label_PK Primary Key " + pkList + ",";

		String fkStr = "Constraint sys_"+ tableName + "_Label_FK Foreign Key ( " + pkCols + ") REFERENCES " + tableName + " " + pkList;

		query = query + " Label character varying(50) NOT NULL, " + constrStr + pkStr + fkStr + ")";

		insertRule = insertRule + "'NOT SET');";

		labelSql = labelSql  + tableName + "_Label.Label IN ')";

		prepareSql( pkCols.toString() , primaryKeys.size());

		queries.add(query);
		queries.add(insertRule);
		queries.add(deleteRule);
		queries.add(labelSql);
		return queries;
	}



	/**
	 *  Sets the sQLStatements attribute of the LabelTableElement object
	 *  Using the tableNames and types
	 *
	 *@return    Returns an ArrayList of statements to be executed.
	 */
	public ArrayList buildSql1(String dataBase)
	{
		ArrayList<String> queries = new ArrayList<String>();

		StringBuffer pkCols = new StringBuffer(" " );

		String createTable =  " CREATE TABLE <tableName>_Label ( <PKeyColTypes> Label varchar(50), Owner varchar(100), <Constraints> " +
	                                  " Constraint sys_<tableName>_Label_PK Primary Key (<PKeys>, Label) , " +
	                                  " Constraint sys_<tableName>_Label_FK Foreign Key (<PKeys>) REFERENCES <tableName>(<PKeys>) ) " ;

		String insertRule = SqlTemplates.getInsertTrigger( dataBase );

	        String deleteRule = SqlTemplates.getDeleteTrigger( dataBase );

		String labelSql = "INSERT INTO LABEL_SQL VALUES ('<tableName>','<labelSql>')";

		String constraintSql = "CONSTRAINT sys_<tableName>_Label_<checkNum> CHECK ((<PKeyCnstrnt>)), ";
		String tmpConstraintSql = "";

		String constraintPk = new String();
		String pKeys = "";
		String labelPks = "";
		String insertRulePk = "";
		String deleteRulePk = "";

		StringBuffer constraints = new StringBuffer();
		//Pkeys need to be formtted in the following way
		//1. create Table: <PKColumn1> <PKColType> NOT NULL,
		//2. Create table: (<PKcolumn1> IS NOT NULL)
		//3. Create table: <PKcolumn1>...
		//4. Delete trigger: <PKCOLUMN1> = old.<PKCOLUMN1> AND <PKCOLUMN2> = old.<PKCOLUMN2>
		//5. INsert trigger: new.<PKCOLUMNS>,
		//6. LabelSql: <TableName>.<PKCOLUMN1> = <TableName>_Label.<PKCOLUMN1>  AND
		for (int i=0; i < primaryKeys.size(); i++)
		{

			String pkCol =  primaryKeys.get(i).toString();

			pkCols.append( pkCol);

			constraintPk =  pkCol + " IS NOT NULL ";
			pKeys = pKeys + pkCol + " " + dataTypeList.get(i).toString() + " NOT NULL, ";

			labelPks =  labelPks +tableName + "." + pkCol + " = " + tableName + "_Label." + pkCol + " AND ";

			insertRulePk = insertRulePk + " new." + pkCol + ",";
			deleteRulePk = deleteRulePk +  pkCol + "= old." + pkCol ;

			if (i < (primaryKeys.size() - 1) )
			{
			    pkCols.append(", ");
			    deleteRulePk = deleteRulePk + " AND ";
			}

			tmpConstraintSql = new String(constraintSql.replaceAll("<PKeyCnstrnt>", constraintPk));
			tmpConstraintSql = tmpConstraintSql.replaceAll("<checkNum>", "CHECK00" + i);

			constraints.append( tmpConstraintSql);
		}

		constraints.append("CONSTRAINT sys_<tableName>_Label_CHECK00" + primaryKeys.size() + " CHECK (('Label' IS NOT NULL )), ");
		labelPks = labelPks  + tableName + "_Label.Label IN ";

		prepareSql( pkCols.toString() , primaryKeys.size());

		//Substitute for each case
		createTable = createTable.replaceAll("<Constraints>", constraints.toString());
		createTable = createTable.replaceAll("<tableName>", tableName);
		createTable = createTable.replaceAll("<PKeyColTypes>", pKeys);
		createTable = createTable.replaceAll("<PKeys>",pkCols.toString());

		labelSql = labelSql.replaceAll("<tableName>", tableName);
		labelSql = labelSql.replaceAll("<labelSql>", labelPks);

		insertRule = insertRule.replaceAll("<insertTriggerPk>", insertRulePk);
		insertRule= insertRule.replaceAll("<tableName>", tableName);
		deleteRule= deleteRule.replaceAll("<deleteTriggerPks>", deleteRulePk);
		deleteRule = deleteRule.replaceAll("<tableName>", tableName);

		queries.add(createTable);
		queries.add(insertRule);
		queries.add(deleteRule);
		queries.add(labelSql);
	        return queries;
	}


	public void prepareSql(String pkCols, int numCols)
	{
	        insertStmnt = "INSERT INTO " + tableName + "_Label VALUES (";

		for (int i=0; i < numCols; i++)
		{
		  insertStmnt = insertStmnt + " ?,";
		}
		insertStmnt = insertStmnt + "'NOT SET','') " ;
		selectRows = "select " + pkCols + " from " + tableName;

	}
	 /**
	 *@return    The elementType value
	 */
	public String getTableName()
	{
		tableName = getNameValues().getValue(FormTags.VALUE_TAG)[0];

		return tableName;
	}

	 /**
	 *@return    The elementType value
	 */
	public ArrayList getPrimaryKeys()
	{

		return primaryKeys;
	}
	 /**
	 *@return    The elementType value
	 */
	public ArrayList getSqlTypeList()
	{
		return sqlTypeList;

	}

	 /**@return    The elementType value
	 */
	public String getSelectRows()
	{

		return selectRows;

	}

	 /**@return    The elementType value
	 */
	public String getInsertStmnt()
	{
		return insertStmnt;
	}
	/**
	 *@return    The elementType value
	 */
	public void setPrimaryKeys(ResultSet rs) throws SQLException
	{
		while (rs.next())
		{
		    primaryKeys.add( rs.getString("COLUMN_NAME"));
		}
	}

	/**
	 *@return    The elementType value
	 */
	public void setColumns(ResultSet rs) throws SQLException
	{
		while (rs.next())
		{
			String colName = rs.getString("COLUMN_NAME");

			//Get the type
			if (primaryKeys.contains( colName ))
			{
			    sqlTypeList.add( new Integer (rs.getInt("DATA_TYPE")) );
			    dataTypeList.add( rs.getString("TYPE_NAME"));
			}
		}

	}

}

